{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "output_type": "stream",
     "name": "stdout",
     "text": [
      "Collecting psycopg2\n",
      "  Downloading psycopg2-2.8.6-cp37-cp37m-win_amd64.whl (1.1 MB)\n",
      "Installing collected packages: psycopg2\n",
      "Successfully installed psycopg2-2.8.6\n",
      "WARNING: You are using pip version 20.1.1; however, version 20.2.3 is available.\n",
      "You should consider upgrading via the 'c:\\users\\zhenchen\\appdata\\local\\programs\\python\\python37\\python.exe -m pip install --upgrade pip' command.\n"
     ]
    }
   ],
   "source": [
    "!pip install psycopg2 "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "%load_ext sql"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "#%sql postgresql://postgres:saturnlab@localhost:5432/bdmi-test"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "output_type": "execute_result",
     "data": {
      "text/plain": [
       "'Connected: gpadmin@testdb'"
      ]
     },
     "metadata": {},
     "execution_count": 5
    }
   ],
   "source": [
    "%sql postgresql://gpadmin:gpdb2020@101.6.160.22:4132/testdb"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Create Movies Table"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "output_type": "stream",
     "name": "stdout",
     "text": [
      " * postgresql://gpadmin:***@101.6.160.22:4132/testdb\n",
      "Done.\n",
      "Done.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n"
     ]
    },
    {
     "output_type": "execute_result",
     "data": {
      "text/plain": [
       "[]"
      ]
     },
     "metadata": {},
     "execution_count": 6
    }
   ],
   "source": [
    "%%sql\n",
    "DROP TABLE IF EXISTS Movies;\n",
    "CREATE TABLE Movies(title VARCHAR(50), year INT, director VARCHAR(50), length INT);\n",
    "INSERT INTO Movies VALUES('algorithm', 1967, 'Joe', 123);\n",
    "INSERT INTO Movies VALUES('data structure'', 1992, 'Bob', 190);\n",
    "INSERT INTO Movies VALUES('database', 1998, 'Jim', 176);"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "output_type": "stream",
     "name": "stdout",
     "text": [
      " * postgresql://gpadmin:***@101.6.160.22:4132/testdb\n3 rows affected.\n"
     ]
    },
    {
     "output_type": "execute_result",
     "data": {
      "text/plain": [
       "[('The Databaser', 1992, 'John Bob', 190),\n",
       " ('Database Wars', 1967, 'John Joe', 123),\n",
       " ('Database Wars', 1998, 'John Jim', 176)]"
      ],
      "text/html": "<table>\n    <tr>\n        <th>title</th>\n        <th>year</th>\n        <th>director</th>\n        <th>length</th>\n    </tr>\n    <tr>\n        <td>The Databaser</td>\n        <td>1992</td>\n        <td>John Bob</td>\n        <td>190</td>\n    </tr>\n    <tr>\n        <td>Database Wars</td>\n        <td>1967</td>\n        <td>John Joe</td>\n        <td>123</td>\n    </tr>\n    <tr>\n        <td>Database Wars</td>\n        <td>1998</td>\n        <td>John Jim</td>\n        <td>176</td>\n    </tr>\n</table>"
     },
     "metadata": {},
     "execution_count": 7
    }
   ],
   "source": [
    "%sql SELECT * FROM movies"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "output_type": "stream",
     "name": "stdout",
     "text": [
      " * postgresql://gpadmin:***@101.6.160.22:4132/testdb\n",
      "Done.\n",
      "Done.\n",
      " * postgresql://gpadmin:***@101.6.160.22:4132/testdb\n",
      "Done.\n",
      "Done.\n",
      " * postgresql://gpadmin:***@101.6.160.22:4132/testdb\n",
      "1 rows affected.\n",
      " * postgresql://gpadmin:***@101.6.160.22:4132/testdb\n",
      "1 rows affected.\n",
      " * postgresql://gpadmin:***@101.6.160.22:4132/testdb\n",
      "1 rows affected.\n",
      " * postgresql://gpadmin:***@101.6.160.22:4132/testdb\n",
      "1 rows affected.\n",
      " * postgresql://gpadmin:***@101.6.160.22:4132/testdb\n",
      "1 rows affected.\n",
      " * postgresql://gpadmin:***@101.6.160.22:4132/testdb\n",
      "1 rows affected.\n",
      " * postgresql://gpadmin:***@101.6.160.22:4132/testdb\n",
      "1 rows affected.\n",
      " * postgresql://gpadmin:***@101.6.160.22:4132/testdb\n",
      "1 rows affected.\n",
      " * postgresql://gpadmin:***@101.6.160.22:4132/testdb\n",
      "1 rows affected.\n"
     ]
    }
   ],
   "source": [
    "%sql DROP TABLE IF EXISTS A; DROP TABLE IF EXISTS B;\n",
    "%sql CREATE TABLE A (x int, y int); CREATE TABLE B (x int, y int);\n",
    "for i in range(1,6):\n",
    "    %sql INSERT INTO A VALUES (:i, :i+1)\n",
    "for i in range(1,11,3):\n",
    "    %sql INSERT INTO B VALUES (:i, :i+2)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "output_type": "stream",
     "name": "stdout",
     "text": [
      " * postgresql://gpadmin:***@101.6.160.22:4132/testdb\n3 rows affected.\n"
     ]
    },
    {
     "output_type": "execute_result",
     "data": {
      "text/plain": [
       "[('Database Wars', 1967, 'John Joe', 123),\n",
       " ('Database Wars', 1998, 'John Jim', 176),\n",
       " ('The Databaser', 1992, 'John Bob', 190)]"
      ],
      "text/html": "<table>\n    <tr>\n        <th>title</th>\n        <th>year</th>\n        <th>director</th>\n        <th>length</th>\n    </tr>\n    <tr>\n        <td>Database Wars</td>\n        <td>1967</td>\n        <td>John Joe</td>\n        <td>123</td>\n    </tr>\n    <tr>\n        <td>Database Wars</td>\n        <td>1998</td>\n        <td>John Jim</td>\n        <td>176</td>\n    </tr>\n    <tr>\n        <td>The Databaser</td>\n        <td>1992</td>\n        <td>John Bob</td>\n        <td>190</td>\n    </tr>\n</table>"
     },
     "metadata": {},
     "execution_count": 9
    }
   ],
   "source": [
    "%sql SELECT * FROM movies"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "output_type": "stream",
     "name": "stdout",
     "text": [
      " * postgresql://gpadmin:***@101.6.160.22:4132/testdb\n2 rows affected.\n"
     ]
    },
    {
     "output_type": "execute_result",
     "data": {
      "text/plain": [
       "[('Database Wars',), ('Database Wars',)]"
      ],
      "text/html": "<table>\n    <tr>\n        <th>title</th>\n    </tr>\n    <tr>\n        <td>Database Wars</td>\n    </tr>\n    <tr>\n        <td>Database Wars</td>\n    </tr>\n</table>"
     },
     "metadata": {},
     "execution_count": 10
    }
   ],
   "source": [
    "%%sql\n",
    "SELECT m.title \n",
    "FROM Movies m\n",
    "WHERE m.year <> ANY(SELECT year FROM Movies WHERE title = m.title);"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "output_type": "stream",
     "name": "stdout",
     "text": [
      " * postgresql://gpadmin:***@101.6.160.22:4132/testdb\n1 rows affected.\n"
     ]
    },
    {
     "output_type": "execute_result",
     "data": {
      "text/plain": [
       "[('Database Wars',)]"
      ],
      "text/html": "<table>\n    <tr>\n        <th>title</th>\n    </tr>\n    <tr>\n        <td>Database Wars</td>\n    </tr>\n</table>"
     },
     "metadata": {},
     "execution_count": 11
    }
   ],
   "source": [
    "%%sql\n",
    "SELECT DISTINCT m1.title\n",
    "FROM movies m1, movies m2\n",
    "WHERE m1.title = m2.title AND m1.year <> m2.year;"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Create Product Table"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "output_type": "stream",
     "name": "stdout",
     "text": [
      " * postgresql://gpadmin:***@101.6.160.22:4132/testdb\n",
      "Done.\n",
      "Done.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n"
     ]
    },
    {
     "output_type": "execute_result",
     "data": {
      "text/plain": [
       "[]"
      ]
     },
     "metadata": {},
     "execution_count": 12
    }
   ],
   "source": [
    "%%sql drop table if exists product;\n",
    "create table product(\n",
    "       pname        varchar primary key, -- name of the product\n",
    "       price        money,               -- price of the product\n",
    "       category     varchar,             -- category\n",
    "       manufacturer varchar NOT NULL     -- manufacturer\n",
    ");\n",
    "insert into product values('Gizmo', 19.99, 'Gadgets', 'GizmoWorks');\n",
    "insert into product values('PowerGizmo', 29.99, 'Gadgets', 'GizmoWorks');\n",
    "insert into product values('SingleTouch', 149.99, 'Photography', 'Canon');\n",
    "insert into product values('MultiTouch', 203.99, 'Household', 'Hitachi');"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "output_type": "stream",
     "name": "stdout",
     "text": [
      " * postgresql://gpadmin:***@101.6.160.22:4132/testdb\n4 rows affected.\n"
     ]
    },
    {
     "output_type": "execute_result",
     "data": {
      "text/plain": [
       "[('Gizmo', '$19.99', 'Gadgets', 'GizmoWorks'),\n",
       " ('SingleTouch', '$149.99', 'Photography', 'Canon'),\n",
       " ('PowerGizmo', '$29.99', 'Gadgets', 'GizmoWorks'),\n",
       " ('MultiTouch', '$203.99', 'Household', 'Hitachi')]"
      ],
      "text/html": "<table>\n    <tr>\n        <th>pname</th>\n        <th>price</th>\n        <th>category</th>\n        <th>manufacturer</th>\n    </tr>\n    <tr>\n        <td>Gizmo</td>\n        <td>$19.99</td>\n        <td>Gadgets</td>\n        <td>GizmoWorks</td>\n    </tr>\n    <tr>\n        <td>SingleTouch</td>\n        <td>$149.99</td>\n        <td>Photography</td>\n        <td>Canon</td>\n    </tr>\n    <tr>\n        <td>PowerGizmo</td>\n        <td>$29.99</td>\n        <td>Gadgets</td>\n        <td>GizmoWorks</td>\n    </tr>\n    <tr>\n        <td>MultiTouch</td>\n        <td>$203.99</td>\n        <td>Household</td>\n        <td>Hitachi</td>\n    </tr>\n</table>"
     },
     "metadata": {},
     "execution_count": 13
    }
   ],
   "source": [
    "%sql select * from product;"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "output_type": "stream",
     "name": "stdout",
     "text": [
      " * postgresql://gpadmin:***@101.6.160.22:4132/testdb\n1 rows affected.\n"
     ]
    },
    {
     "output_type": "execute_result",
     "data": {
      "text/plain": [
       "[('PowerGizmo', '$29.99', 'Gadgets', 'GizmoWorks')]"
      ],
      "text/html": "<table>\n    <tr>\n        <th>pname</th>\n        <th>price</th>\n        <th>category</th>\n        <th>manufacturer</th>\n    </tr>\n    <tr>\n        <td>PowerGizmo</td>\n        <td>$29.99</td>\n        <td>Gadgets</td>\n        <td>GizmoWorks</td>\n    </tr>\n</table>"
     },
     "metadata": {},
     "execution_count": 18
    }
   ],
   "source": [
    "%%sql\n",
    "SELECT * from Product \n",
    "WHERE category='Gadgets' and price > 20.0::money;"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "output_type": "stream",
     "name": "stdout",
     "text": [
      " * postgresql://gpadmin:***@101.6.160.22:4132/testdb\n4 rows affected.\n"
     ]
    },
    {
     "output_type": "execute_result",
     "data": {
      "text/plain": [
       "[('PowerGizmo', '$29.99', 'GizmoWorks'),\n",
       " ('MultiTouch', '$203.99', 'Hitachi'),\n",
       " ('Gizmo', '$19.99', 'GizmoWorks'),\n",
       " ('SingleTouch', '$149.99', 'Canon')]"
      ],
      "text/html": "<table>\n    <tr>\n        <th>pname</th>\n        <th>price</th>\n        <th>manufacturer</th>\n    </tr>\n    <tr>\n        <td>PowerGizmo</td>\n        <td>$29.99</td>\n        <td>GizmoWorks</td>\n    </tr>\n    <tr>\n        <td>MultiTouch</td>\n        <td>$203.99</td>\n        <td>Hitachi</td>\n    </tr>\n    <tr>\n        <td>Gizmo</td>\n        <td>$19.99</td>\n        <td>GizmoWorks</td>\n    </tr>\n    <tr>\n        <td>SingleTouch</td>\n        <td>$149.99</td>\n        <td>Canon</td>\n    </tr>\n</table>"
     },
     "metadata": {},
     "execution_count": 19
    }
   ],
   "source": [
    "%%sql \n",
    "SELECT Pname, Price, Manufacturer\n",
    "FROM Product;"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "output_type": "stream",
     "name": "stdout",
     "text": [
      " * postgresql://gpadmin:***@101.6.160.22:4132/testdb\n2 rows affected.\n"
     ]
    },
    {
     "output_type": "execute_result",
     "data": {
      "text/plain": [
       "[('Gizmo', '$19.99', 'GizmoWorks'), ('PowerGizmo', '$29.99', 'GizmoWorks')]"
      ],
      "text/html": "<table>\n    <tr>\n        <th>pname</th>\n        <th>price</th>\n        <th>manufacturer</th>\n    </tr>\n    <tr>\n        <td>Gizmo</td>\n        <td>$19.99</td>\n        <td>GizmoWorks</td>\n    </tr>\n    <tr>\n        <td>PowerGizmo</td>\n        <td>$29.99</td>\n        <td>GizmoWorks</td>\n    </tr>\n</table>"
     },
     "metadata": {},
     "execution_count": 20
    }
   ],
   "source": [
    "%%sql\n",
    "SELECT Pname, Price, Manufacturer\n",
    "FROM Product\n",
    "WHERE category='Gadgets';"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "output_type": "stream",
     "name": "stdout",
     "text": [
      " * postgresql://gpadmin:***@101.6.160.22:4132/testdb\n4 rows affected.\n1 rows affected.\n"
     ]
    },
    {
     "output_type": "execute_result",
     "data": {
      "text/plain": [
       "[('GizmoWorks', 'PowerGizmo', '$29.99')]"
      ],
      "text/html": "<table>\n    <tr>\n        <th>manufacturer</th>\n        <th>pname</th>\n        <th>price</th>\n    </tr>\n    <tr>\n        <td>GizmoWorks</td>\n        <td>PowerGizmo</td>\n        <td>$29.99</td>\n    </tr>\n</table>"
     },
     "metadata": {},
     "execution_count": 23
    }
   ],
   "source": [
    "%%sql\n",
    "SELECT * FROM Product;\n",
    "\n",
    "SELECT\n",
    "    p.manufacturer, p.pname, p.price\n",
    "FROM \n",
    "    (SELECT distinct p0.Manufacturer\n",
    "     FROM Product p0\n",
    "     WHERE p0.price < 20.00::money) cp, -- this is a nested query!\n",
    "    Product p\n",
    "WHERE \n",
    "    p.manufacturer = cp.manufacturer and p.price >= 20.00::money"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Create Company Table"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "output_type": "stream",
     "name": "stdout",
     "text": [
      " * postgresql://gpadmin:***@101.6.160.22:4132/testdb\n",
      "Done.\n",
      "Done.\n",
      "Done.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n"
     ]
    },
    {
     "output_type": "execute_result",
     "data": {
      "text/plain": [
       "[]"
      ]
     },
     "metadata": {},
     "execution_count": 24
    }
   ],
   "source": [
    "%%sql\n",
    "drop table if exists product; -- This needs to be dropped if exists, see why further down!\n",
    "drop table if exists company;\n",
    "create table company (\n",
    "    cname varchar primary key, -- company name uniquely identifies the company.\n",
    "    stockprice money, -- stock price is in money \n",
    "    country varchar); -- country is just a string\n",
    "insert into company values ('GizmoWorks', 25.0, 'USA');\n",
    "insert into company values ('Canon', 65.0, 'Japan');\n",
    "insert into company values ('Hitachi', 15.0, 'Japan');"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "output_type": "stream",
     "name": "stdout",
     "text": [
      " * postgresql://gpadmin:***@101.6.160.22:4132/testdb\n3 rows affected.\n"
     ]
    },
    {
     "output_type": "execute_result",
     "data": {
      "text/plain": [
       "[('Canon', '$65.00', 'Japan'),\n",
       " ('GizmoWorks', '$25.00', 'USA'),\n",
       " ('Hitachi', '$15.00', 'Japan')]"
      ],
      "text/html": "<table>\n    <tr>\n        <th>cname</th>\n        <th>stockprice</th>\n        <th>country</th>\n    </tr>\n    <tr>\n        <td>Canon</td>\n        <td>$65.00</td>\n        <td>Japan</td>\n    </tr>\n    <tr>\n        <td>GizmoWorks</td>\n        <td>$25.00</td>\n        <td>USA</td>\n    </tr>\n    <tr>\n        <td>Hitachi</td>\n        <td>$15.00</td>\n        <td>Japan</td>\n    </tr>\n</table>"
     },
     "metadata": {},
     "execution_count": 25
    }
   ],
   "source": [
    "%sql select * from company;"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "output_type": "stream",
     "name": "stdout",
     "text": [
      " * postgresql://gpadmin:***@101.6.160.22:4132/testdb\n",
      "Done.\n",
      "Done.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n"
     ]
    },
    {
     "output_type": "execute_result",
     "data": {
      "text/plain": [
       "[]"
      ]
     },
     "metadata": {},
     "execution_count": 26
    }
   ],
   "source": [
    "%%sql drop table if exists product;\n",
    "create table product(\n",
    "       pname varchar primary key, -- name of the product\n",
    "       price money, -- price of the product\n",
    "       category varchar, -- category\n",
    "       manufacturer varchar, -- manufacturer\n",
    "       foreign key (manufacturer) references company(cname));\n",
    "\n",
    "insert into product values('Gizmo', 19.99, 'Gadgets', 'GizmoWorks');\n",
    "insert into product values('PowerGizmo', 29.99, 'Gadgets', 'GizmoWorks');\n",
    "insert into product values('SingleTouch', 149.99, 'Photography', 'Canon');\n",
    "insert into product values('MultiTouch', 203.99, 'Household', 'Hitachi');"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "output_type": "stream",
     "name": "stdout",
     "text": [
      " * postgresql://gpadmin:***@101.6.160.22:4132/testdb\n(psycopg2.errors.UniqueViolation) duplicate key value violates unique constraint \"product_pkey\"  (seg1 10.0.1.43:6000 pid=14703)\nDETAIL:  Key (pname)=(MultiTouch) already exists.\n\n[SQL: insert into product values('MultiTouch', 203.99, 'Household', 'Google');]\n(Background on this error at: http://sqlalche.me/e/gkpj)\nRejected!\n"
     ]
    }
   ],
   "source": [
    "try:\n",
    "    %sql insert into product values('MultiTouch', 203.99, 'Household', 'Google');\n",
    "except Exception as e:\n",
    "    print(e) \n",
    "    print(\"Rejected!\") "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Joins"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "output_type": "stream",
     "name": "stdout",
     "text": [
      " * postgresql://gpadmin:***@101.6.160.22:4132/testdb\n2 rows affected.\n"
     ]
    },
    {
     "output_type": "execute_result",
     "data": {
      "text/plain": [
       "[('SingleTouch', '$149.99'), ('MultiTouch', '$203.99')]"
      ],
      "text/html": "<table>\n    <tr>\n        <th>pname</th>\n        <th>price</th>\n    </tr>\n    <tr>\n        <td>SingleTouch</td>\n        <td>$149.99</td>\n    </tr>\n    <tr>\n        <td>MultiTouch</td>\n        <td>$203.99</td>\n    </tr>\n</table>"
     },
     "metadata": {},
     "execution_count": 28
    }
   ],
   "source": [
    "%%sql\n",
    "SELECT pname, price\n",
    "FROM product, company\n",
    "where manufacturer=cname and country='Japan';"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "output_type": "stream",
     "name": "stdout",
     "text": [
      " * postgresql://gpadmin:***@101.6.160.22:4132/testdb\n2 rows affected.\n"
     ]
    },
    {
     "output_type": "execute_result",
     "data": {
      "text/plain": [
       "[('USA',), ('USA',)]"
      ],
      "text/html": "<table>\n    <tr>\n        <th>country</th>\n    </tr>\n    <tr>\n        <td>USA</td>\n    </tr>\n    <tr>\n        <td>USA</td>\n    </tr>\n</table>"
     },
     "metadata": {},
     "execution_count": 29
    }
   ],
   "source": [
    "%%sql -- duplicate answer\n",
    "SELECT Country\n",
    "FROM Product, Company\n",
    "WHERE  Manufacturer=CName AND Category='Gadgets';"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "%%sql -- duplicate answer\n",
    "SELECT DISTINCT Country\n",
    "FROM Product, Company\n",
    "WHERE  Manufacturer=CName AND Category='Gadgets';"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.7.5-final"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}